<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Map_markers_model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
    }

    public function get_markers(){
        $sql="SELECT lookup_code, meaning, description FROM app_lookup_values where lookup_type = 'MAP_MARKERS' 
                    and lookup_code not in (SELECT marker_code FROM ubs_marker_range)";
        $query = $this->db->query($sql);
        return $query->result();
    }

    public function insert_markers($data){
        $this->db->query('call ubs_insert_mark(?,?,?,?,@err,@msg)',$data);
        $query = $this->db->query("SELECT @err as error , @msg as msg");
        return $query->result();
    }

    public function delete_range($data){
        $this->db->query('call ubs_mark_range_del(?,@err,@msg)',$data);
        $query = $this->db->query("SELECT @err as error , @msg as msg");
        return $query->result();
    }

    public function check_range($num){
        $sql = "SELECT 
                    (SELECT(CASE WHEN ".$num." BETWEEN range_from and range_to THEN 'NO' ELSE 'GO' END)) as status,
                    (SELECT(CASE WHEN ".$num." BETWEEN range_from and range_to THEN 1    ELSE  0   END)) as error,
                    (SELECT(CASE WHEN ".$num." BETWEEN range_from and range_to THEN 'Invalid Range' ELSE null END)) as msg
                 from ubs_marker_range
                    WHERE ".$num." BETWEEN range_from and range_to GROUP BY status";
    
    $query = $this->db->query($sql);

    if ($query->num_rows() > 0) {
        return $query->result();
    } else {
        $query1 = $this->db->query("SELECT '0' as error , 'null' as msg");
        return $query1->result();
    }

     
    }

    public function get_all(){
        $sql = "SELECT umr.* , alv.description 
                FROM ubs_marker_range umr
                    INNER JOIN app_lookup_values alv on alv.lookup_code = umr.marker_code 
                ORDER BY umr.range_from ASC";
        $query = $this->db->query($sql);
        echo json_encode($query->result());
    }

}